package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import utils.JdbcUtil;
import model.Field;

public class FieldDao {
	
	/**
	 * 根据field的id，获取所有拥有相同tId(表Id)的其他field,即获取同表内其他的field
	 * @param fId
	 * @return
	 */
	public List<Field> getFieldListByFieldId(String fId){
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
        
		String sql = "select id,fieldName,tId "+ 
				" FROM t_fields f "+
				"where f.id!=%s and f.tId in (select tid from t_fields f where f.id = %s)";
		sql = String.format(sql, fId,fId);
		
		List<Field> fieldList = null;
		try {
			con = JdbcUtil.getCon();
			fieldList = new ArrayList<Field>();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Field f = new Field();
				f.setId(rs.getInt("id"));
				f.setFieldName(rs.getString("fieldName"));
				f.settId(rs.getInt("tId"));
				
				fieldList.add(f);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return fieldList;
	}
	public static void main(String[] args) {
		FieldDao fd = new FieldDao();
		List<Field> fieldListByFieldId = fd.getFieldListByFieldId("0");
		for (Field field : fieldListByFieldId) {
			System.out.println(field);
		}
	}

	public List<Field> getFieldListByTid(String tId){
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
        
		List<Field> fieldList = null;
		try {
			con = JdbcUtil.getCon();
			String sql = "select * from t_fields where tId = " + tId;
			fieldList = new ArrayList<Field>();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Field f = new Field();
				f.setId(rs.getInt("id"));
				f.setFieldName(rs.getString("fieldName"));
				f.settId(rs.getInt("tId"));
				f.setType(rs.getString("type"));
//				f.setCreateTime("createTime");
//				f.setLocation("location");
				fieldList.add(f);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return fieldList;
	}

	public int addField(Field field){
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
        
        int result = -1;
		try {
			con = JdbcUtil.getCon();
			String sql = "insert into t_fields (fieldName,tid,type) values(?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, field.getFieldName());
			pstmt.setInt(2, field.gettId());
			pstmt.setString(3, field.getType());
	//		pstmt.setString(4, field.getLocation());
			result =  pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return result;
	}

	public List<Field> getAllFieldList() {
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
        List<Field> fieldList = new ArrayList<Field>();
        
		try {
			String sql = "select * from t_fields order by tId";
			con = JdbcUtil.getCon();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Field f = new Field();
				f.setId(rs.getInt("id"));
				f.setFieldName(rs.getString("fieldName"));
				f.settId(rs.getInt("tId"));
				
				fieldList.add(f);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return fieldList;
	}

	public int deleteField(String id) {
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
		
		String sql = "delete from dbo.t_fields where id =" + id;
		int result = -1;
		try {
			con = JdbcUtil.getCon();
			pstmt = con.prepareStatement(sql);
			result =  pstmt.executeUpdate();
		} catch (SQLException e) {
			return 0;
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return result;
	}
	public List<Field> getFieldListbyTableId(String name){
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
		String sql = "select * from t_fields where tId like '%"+ name+"%'";
		List<Field> fieldList = new ArrayList<Field>();
		
		try {
			con = JdbcUtil.getCon();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Field f = new Field();
				f.setId(rs.getInt("id"));
				f.setFieldName(rs.getString("fieldName"));
				f.settId(rs.getInt("tId"));
				fieldList.add(f);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return fieldList;
	}
	
	public Field getFieldNameByTidAndType(int tId, String type){
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
        String sql = "select * from t_fields where tid=? and type=?";
        Field f = null ;
        try {
			con = JdbcUtil.getCon();
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, tId);
			pstmt.setString(2, type);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				f =  new Field();
				f.setId(rs.getInt("id"));
				f.setFieldName(rs.getString("fieldName"));
				f.settId(rs.getInt("tId"));
				f.setType(rs.getString("type"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
        return f;

	}
	
	public List<Field> getFieldListbyTidAndType(String name,String fieldType){
		Connection con =null;  
        PreparedStatement pstmt = null;  
        ResultSet rs = null;
		String sql = "select * from t_fields where tId = '"+ name+"'"+" and type ="+fieldType;
		List<Field> fieldList = new ArrayList<Field>();
		
		try {
			con = JdbcUtil.getCon();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Field f = new Field();
				f.setId(rs.getInt("id"));
				f.setFieldName(rs.getString("fieldName"));
				f.settId(rs.getInt("tId"));
				fieldList.add(f);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeAll(rs, pstmt,con);
		}
		return fieldList;
	}
	
}
